<?php

class ThongKeDonHangController extends Controller
{
	public function actionIndex()
	{
		$this->render('index');
	}
	public function actionThongKeDonHang(){

		if(isset($_GET['thang']) && isset($_GET['nam'])){
			$thang = intval($_GET['thang']);
			$nam = intval($_GET['nam']);
			$sql = "
					SELECT
						DAY(o.order_date) as ngay,MONTH(o.order_date) as thang,SUM((od.quantity * p.price)) as thanhtien,YEAR(o.order_date) as nam
					FROM
						orders o,
						orderdetails od,
						products p
					WHERE
						o.id = od.order_id
					AND p.id = od.product_id
			
					AND (
						MONTH (o.order_date) = $thang
						AND YEAR (o.order_date) = $nam
					)
					GROUP BY ngay
					ORDER BY o.order_date ASC";

			$list= Yii::app()->db->createCommand($sql)->queryAll();
			echo json_encode(array('lists'=>$list));
		}else{
			echo json_encode(array('lists'=>''));
		}
	}
	public function actionThongKeSanPham(){

		if(isset($_GET['thang']) && isset($_GET['nam'])){
			$thang = intval($_GET['thang']);
			$nam = intval($_GET['nam']);
			$sql = "
			SELECT
				p.product_name,
				c.cate_name,
				COUNT(product_name) * od.quantity AS solanmua,
				SUM(p.price * od.quantity) AS total,
				MONTH(o.order_date) as thang,
				YEAR(o.order_date) as nam
			FROM
				products p,
				orders o,
				orderdetails od,
				categories c
			WHERE
				p.id = od.product_id
			AND o.id = od.order_id
			AND c.id = p.cate_id
			AND (
				MONTH (o.order_date) = $thang
				AND YEAR (o.order_date) = $nam
			)
			GROUP BY
				product_name
			ORDER BY
				solanmua";

				// print_r($sql);

			$list= Yii::app()->db->createCommand($sql)->queryAll();
			echo json_encode(array('lists'=>$list));
		}else{
			echo json_encode(array('lists'=>''));
		}

	}
	public function actionThongTinDasboard(){
			$thang = date("m");
			$nam = date("Y");
			$ngay = date("d");
			$sql = "
			(
				SELECT
					SUM(o.total) AS total
				FROM
					orders o
				WHERE
					MONTH (o.order_date) = $thang
				AND YEAR (o.order_date) = $nam
				AND DAY (o.order_date) = $ngay
			)
			UNION ALL
				(
					SELECT
						SUM(o.total) AS total
					FROM
						orders o
					WHERE
						MONTH (o.order_date) = $thang
					AND YEAR (o.order_date) = $nam
					AND DAY (o.order_date) = $ngay
					AND o.`status` = 1
				)
			UNION ALL
				(
					SELECT
						SUM(o.total) AS total
					FROM
						orders o
					WHERE
						MONTH (o.order_date) = $thang
					AND YEAR (o.order_date) = $nam
				)
			UNION ALL
				(
					SELECT
						COUNT(o.id) AS total
					FROM
						orders o
					WHERE
						MONTH (o.order_date) = $thang
						AND YEAR (o.order_date) = $nam
						AND DAY (o.order_date) = $ngay
				)";

			$list= Yii::app()->db->createCommand($sql)->queryAll();
			echo json_encode(array("success" => true,'lists'=>$list));
	}
	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
}